import shutil
from zipfile import ZipFile
from xml.dom.minidom import parse
import re
from pao.models import Temporal
from datetime import datetime
import hashlib
class XlsxLoader(object):
    """
    Descomponer en su estructura el archivo .xlsx, para tomar la
    parte correspondiente a directorio xl.
    Los archivos a examinar son:
    xl/workbook.xml
    xl/sharedStrings.xml
    xl/worksheets/sheet*.xml
    (Para el caso especifico, solo se tomara en cuenta el xl/worksheets/sheet1.xml)
    """
    UNZIP_PATH = 'extracto/'
    
    def __init__(self, xlsxFile):
        """
        Asignar el nombre del archivo xlsx
        """
        #print "Passing: %s file to constructor..." % xlsxFile
        self.xlsxFile = xlsxFile
    def extract(self):
        """
        Extraer la estructura del archivo xlsx en el UNZIP_PATH
        """
        #print "Extracting estructure file into %s" % self.UNZIP_PATH
        zip = ZipFile(self.xlsxFile)
        zip.extractall(self.UNZIP_PATH)
        wb = XlsxParser(self.UNZIP_PATH, self.xlsxFile)
        wb.readWorkbook()
        #print d 
        #r = wb.readRelations()
        s = wb.readSharedStrings()
        #t = wb.readSheets()
        # retorna 0 normal, > 0 problemas
        wb.populateTableTemporal('sheet1.xml')
        wb.populateTableTemporal('sheet2.xml')
        
        zip.close()
        
    def __del__(self):
        try:
            shutil.rmtree(self.UNZIP_PATH)
            #print "deleting tree directory. Uncomment this for availability"
        except:
            pass
        finally:
            pass
    
class XlsxParser(object):
    
    def __init__(self, unzip_path, file_name):
        #self.xlsxfile = filename
        self.unzip_path = unzip_path
        self.wb_path = self.unzip_path + 'xl/workbook.xml'
        self.ss_path = self.unzip_path + 'xl/sharedStrings.xml'
        self.rels_wb_path = self.unzip_path + 'xl/_rels/workbook.xml.rels'
        self.sharedStrings=[]
        self.sheetNumber = self.unzip_path+"xl/worksheets/"
        self.latest_added = datetime.now()
        self.file_name = file_name
        
        
    """
    Read and assign xml objeto to variable. process
    """
    def readWorkbook(self):
       
        #print "Read-ing (%s) filename...." % self.wb_path
        sheets={}
        try:
            dom = parse(self.wb_path)
            #print dom
            # Al menos dos hojas deberan estar presentes
            # 
            for sheet in dom.getElementsByTagName('sheet'):
                identificador = sheet.getAttribute('r:id') 
                nombre = sheet.getAttribute('sheetId')
                clave ='sheet' + nombre
                 
                #print "(%s)sheetId: %s , in _rels: %s" % (clave,nombre, identificador)
                sheets[clave] = identificador
            return sheets
        except IOError, e:
            print e
    def readRelations(self):
        #print "Reading (%s) file for relations..." % self.rels_wb_path
        rels = {}
        try:
            dom = parse(self.rels_wb_path)
          
            for rel in dom.getElementsByTagName('Relationship'):
                sheetId = rel.getAttribute('Target')
                relId = rel.getAttribute('Id')
                #print "Id: %s, Target: %s " % (relId, sheetId)
                rels[relId] = sheetId
            return rels
        except IOError, e:
            print e
    def readSharedStrings(self):
        print "Reading sharedStrings from (%s)..." % self.ss_path
        
        
        try:
            dom = parse(self.ss_path)
            for node in dom.firstChild.childNodes:
                #print "Processing NODES..."
                try:
                    nodo = node.firstChild.firstChild.nodeValue
                    self.sharedStrings.append(nodo)
                    #print nodo
                except AttributeError:
                    self.sharedStrings.append('')
            
        except IOError, e:
            print e
    def readSheets(self, sheetIndex):
        """
        Generalmente las columnas C28, K28, L28 contienen los valores que necesitamos
        almacenar el la tabla temporal. la hoja es la sheet1
        """
        tabla = []
        dom = parse(sheetIndex)
        idx=0
        for row in dom.getElementsByTagName('row'):
            
            record = {}
            is_rfc=None
            metas=0
            factores=0
            nombre = "None"
            
            for col in row.childNodes:
                colName = col.getAttribute('r')
                
                r = re.search("^[CDKL]{1}\d{1,3}",colName)
                if r:
                    #
                    #print "Columna %s "%colName
                    try:
                        valor  = col.firstChild.firstChild.nodeValue
                        #print "%s=%s" % (colName, valor)
                        if colName[0:1]=='C':
                            if col.getAttribute('t')=='s':
                                is_rfc= self.sharedStrings[int(valor)]
                                #pass
                        if colName[0:1]=='K':
                            metas = valor
                        if colName[0:1]=='L':
                            factores = valor
                        if colName[0:1]=='D':
                            if col.getAttribute('t')=='s':
                                nombre= self.sharedStrings[int(valor)][:100]
                    except AttributeError:
                        valor=None
                    
            if is_rfc:
                f = re.search("^[A-Za-z]{4}\d{6}", is_rfc)
                if f:
                    #
                    record= {'id':idx, 'rfc':is_rfc, 'nombre':nombre,'metas':metas, 'factores':factores}
                    tabla.insert(idx, record)
                    idx+=1
                
                #print "%s\trfc:%s\tmetas=%s\tfactores:%s" % (idx,is_rfc, metas, factores)
                
                    
        return tabla 
    def populateTableTemporal(self, sheetIndex):
        filas = self.readSheets(self.sheetNumber + sheetIndex)
        if filas is not None:
            for f in filas:
                idx = f.get('id')
                rfc= f.get('rfc')
                metas = f.get('metas')
                factores = f.get('factores')
                fullname = f.get('nombre')
                hid = str(hashlib.md5(str(self.file_name)).hexdigest())
                    #print id,rfc,metas,factores
                try:
                    newRecord = Temporal(id=idx,
                                             rfc=rfc,
                                             metas=metas,
                                             factores=factores,
                                             latest_add=self.latest_added,
                                             file_name=self.file_name,
                                             hashid=hid,
                                             nombre=fullname)
                    newRecord.save()
                except:
                    print "No saved!!"
            